Imports System
Imports System.Collections
Imports System.Text

Public Class Query

#Region "Properties"

	Private Sql As String

	Private _DataHandlerObject As DataHandler
	Private _CommandType As CommandTypeEnum = UniDataTier.CommandTypeEnum.None
	Private _ReturnIdentity As Boolean = True
	Private _DataFields As New ArrayList
	Private _WhereFields As New ArrayList
	Private _Table As String

	Public Property DataHandlerObject() As DataHandler
		Get
			Return _DataHandlerObject
		End Get
		Set(ByVal value As DataHandler)
			_DataHandlerObject = value
		End Set
	End Property

	Public Property CommandType() As CommandTypeEnum
		Get
			Return _CommandType
		End Get
		Set(ByVal value As CommandTypeEnum)
			_CommandType = value
		End Set
	End Property

	Public Property ReturnIdentity() As Boolean
		Get
			Return _ReturnIdentity
		End Get
		Set(ByVal value As Boolean)
			_ReturnIdentity = value
		End Set
	End Property

	Public Property DataFields() As ArrayList
		Get
			Return _DataFields
		End Get
		Set(ByVal value As ArrayList)
			_DataFields = value
		End Set
	End Property

	Public Property WhereFields() As ArrayList
		Get
			Return _WhereFields
		End Get
		Set(ByVal value As ArrayList)
			_WhereFields = value
		End Set
	End Property

	Public Property Table() As String
		Get
			Return _Table
		End Get
		Set(ByVal value As String)
			_Table = value
		End Set
	End Property

	Public Overrides Function ToString() As String

		GenerateSQL()

		Return Sql

	End Function

#End Region

#Region "Constructors"

	Public Sub New()

	End Sub

	Public Sub New(ByVal objCommand As CommandTypeEnum)

		Me.CommandType = objCommand

	End Sub

	Public Sub New(ByVal objCommand As CommandTypeEnum, ByVal strTable As String)

		Me.CommandType = objCommand
		Me.Table = strTable

	End Sub

#End Region

#Region "Add fields"

	Public Sub AddField(ByVal strField As String)

		AddField(strField, String.Empty)

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal strValue As String)

		AddField(strField, strValue, "String", Config.QueryFieldNullable)

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal strValue As String, ByVal blnNullable As Boolean)

		AddField(strField, strValue, "String", blnNullable)

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal intValue As Integer)

		AddField(strField, intValue.ToString(), "Integer", Config.QueryFieldNullable)

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal intValue As Integer, ByVal blnNullable As Boolean)

		AddField(strField, intValue.ToString(), "Integer", blnNullable)

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal blnValue As Boolean)

		AddField(strField, blnValue.ToString(), "Boolean", Config.QueryFieldNullable)

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal blnValue As Boolean, ByVal blnNullable As Boolean)

		AddField(strField, blnValue.ToString(), "Boolean", blnNullable)

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal dtmValue As DateTime)

		If dtmValue > DateTime.MinValue Then
			AddField(strField, dtmValue.ToString("yyyy-MM-dd HH:mm:ss"), "DateTime", Config.QueryFieldNullable)
		Else
			AddField(strField)
		End If

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal dtmValue As DateTime, ByVal blnNullable As Boolean)

		If dtmValue > DateTime.MinValue Then
			Me.AddField(strField, dtmValue.ToString("yyyy-MM-dd HH:mm:ss"), "DateTime", blnNullable)
		Else
			Me.AddField(strField)
		End If

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal strValue As String, ByVal strType As String)

		AddField(strField, strValue, strType, Config.QueryFieldNullable)

	End Sub

	Public Sub AddField(ByVal strField As String, ByVal strValue As String, ByVal strType As String, ByVal blnNullable As Boolean)

		Dim objField As New DatabaseField

		objField.Name = strField
		objField.Value = strValue
		objField.Type = strType
		objField.Nullable = blnNullable

		DataFields.Add(objField)

	End Sub

#End Region

#Region "Add where conditions"

	Public Sub AddWhere(ByVal strField As String)

		Me.AddWhere(strField, String.Empty, "String", Config.QueryFieldNullable)

	End Sub

	Public Sub AddWhere(ByVal strField As String, ByVal strValue As String)

		Me.AddWhere(strField, strValue, "String", Config.QueryFieldNullable)

	End Sub

	Public Sub AddWhere(ByVal strField As String, ByVal intValue As Integer)

		Me.AddWhere(strField, intValue.ToString(), "Integer", Config.QueryFieldNullable)

	End Sub

	Public Sub AddWhere(ByVal strField As String, ByVal blnValue As Boolean)

		Me.AddWhere(strField, blnValue.ToString(), "Boolean", Config.QueryFieldNullable)

	End Sub

	Public Sub AddWhere(ByVal strField As String, ByVal dtmValue As DateTime)

		If dtmValue > DateTime.MinValue Then
			Me.AddWhere(strField, dtmValue.ToString("yyyy-MM-dd HH:mm:ss"), "DateTime", Config.QueryFieldNullable)
		Else
			Me.AddWhere(strField)
		End If

	End Sub

	Public Sub AddWhere(ByVal strField As String, ByVal strValue As String, ByVal strType As String, ByVal blnNullable As Boolean)

		Dim objField As New DatabaseField

		objField.Name = strField
		objField.Value = strValue
		objField.Type = strType
		objField.Nullable = blnNullable

		WhereFields.Add(objField)

	End Sub

#End Region

#Region "Generate SQL"

	Private Sub GenerateSQL()

		Dim objStrFields As New StringBuilder
		Dim objStrWhere As New StringBuilder
		Dim objString1 As New StringBuilder
		Dim objString2 As New StringBuilder
		Dim objField As DatabaseField
		Dim strValue As String

		Select Case CommandType

			Case CommandTypeEnum.Insert

				objString1.Append("(")
				objString2.Append("(")
				objStrFields.Append("INSERT INTO " & _Table & "")

				For Each objField In DataFields
					strValue = DataHandler.HandleValue(objField.Value, objField.Type)
					If Not objField.Nullable Or (Not objField.Value Is Nothing AndAlso objField.Value.Length > 0) Then
						objString1.Append(objField.Name & ",")
						objString2.Append(strValue & ",")
					End If
				Next

				objString1.Remove(objString1.Length - 1, 1)
				objString2.Remove(objString2.Length - 1, 1)
				objString1.Append(")")
				objString2.Append(")")

				objStrFields.Append(objString1.ToString())
				objStrFields.Append(" VALUES ")
				objStrFields.Append(objString2.ToString())

				If _ReturnIdentity Then
					If Not DataHandlerObject.ConnectionString.ToLower().Contains("mysql") And Not DataHandlerObject.ConnectionString.ToLower().Contains("myoledb") Then
						objStrFields.Append(";")
						objStrFields.Append(Environment.NewLine)
						objStrFields.Append("SELECT @@Identity")
					End If
				End If

				Sql = objStrFields.ToString()

			Case CommandTypeEnum.Update

				objStrFields.Append("UPDATE " & _Table & " SET ")

				For Each objField In DataFields
					strValue = DataHandler.HandleValue(objField.Value, objField.Type)
					If Not objField.Nullable Or (Not objField.Value Is Nothing AndAlso objField.Value.Length > 0) Then
						objStrFields.Append(objField.Name & "=" & strValue & ",")
					Else
						objStrFields.Append(objField.Name & "=NULL,")
					End If
				Next

				If objStrFields.Length > 4 Then
					objStrFields.Remove(objStrFields.Length - 1, 1)
				End If

				For Each objField In WhereFields
					strValue = DataHandler.HandleValue(objField.Value, objField.Type)
					If Not objField.Nullable Or objField.Value.Length > 0 Then
						objStrWhere.Append(" " & objField.Name & " = " & strValue & " AND ")
					Else
						objStrWhere.Append(" " & objField.Name & " IS NULL AND ")
					End If
				Next

				If WhereFields.Count > 0 Then
					objStrWhere.Remove(objStrWhere.Length - 4, 4)
					Sql = objStrFields.ToString() & " WHERE " & objStrWhere.ToString()
				Else
					Sql = objStrFields.ToString()
				End If

		End Select

	End Sub

#End Region

#Region "Execute command"

	Public Function Execute() As Integer

		Return Execute(New DataHandler, True)

	End Function

	Public Function Execute(ByVal objDataHandler As DataHandler) As Integer

		DataHandlerObject = objDataHandler

		Return Execute(objDataHandler, False)

	End Function

	Public Function Execute(ByVal objDataHandler As DataHandler, ByVal blnDispose As Boolean) As Integer

		DataHandlerObject = objDataHandler

		Dim blnAutoClose As Boolean = objDataHandler.AutoClose
		Dim intReturn As Integer

		If _CommandType = UniDataTier.CommandTypeEnum.None Then
			Throw New UniDataTierException(GetType(Query), My.Resources.Messages.ErrorCommandNotSpecified)
		ElseIf _Table.Length < 0 Then
			Throw New UniDataTierException(GetType(Query), My.Resources.Messages.ErrorTableNotSpecified)
		End If

		DataHandlerObject.AutoClose = blnDispose

		Try

			GenerateSQL()

			If CommandType = UniDataTier.CommandTypeEnum.Insert AndAlso ReturnIdentity Then
				If DataHandlerObject.ConnectionString.ToLower().Contains("mysql") Or DataHandlerObject.ConnectionString.ToLower().Contains("myoledb") Then
					DataHandlerObject.ExecuteScalar(Sql)
					intReturn = Convert.ToInt32(DataHandlerObject.ExecuteScalar("SELECT LAST_INSERT_ID()"))
				Else
					intReturn = Convert.ToInt32(DataHandlerObject.ExecuteScalar(Sql))
				End If
			Else
				intReturn = DataHandlerObject.ExecuteNonQuery(Sql) + 1
			End If

		Catch ex As Exception

			Throw

		End Try

		If blnDispose Then
			objDataHandler.Close()
		End If

		Return intReturn

	End Function

#End Region

End Class